﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Common;
using System.Data.Common;
using System.Data;
using Musicman.Common.Entities;

namespace Musicman.DAL
{
    public class SearchJobDAO : DAOBase
    {
        # region singleton definition
        static readonly SearchJobDAO instance = new SearchJobDAO();

        public static SearchJobDAO Instance
        {
            get
            {
                return instance;
            }
        }
        static SearchJobDAO()
        {
        }
        # endregion

        #region select
        
        public long GetSearchJobID(string jobName)
        {
            long res = -1;
            string selectSQL = "SELECT SearchJobID FROM SearchJob where name = @JOBANME";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@JOBANME", DbType.String, jobName);

            object objRes = db.ExecuteScalar(cmd);
            if (objRes != null)
                res = (long)objRes;

            return res;
        }

        public List<long> GetInactiveJobIds(List<long> activeJobIDs)
        {
            List<long> res = new List<long>();
            IDataReader reader = null;
            try
            {
                StringBuilder sql = new StringBuilder("select SearchJobId from searchjob where searchJobId not in (");
                for (int i = 0; i < activeJobIDs.Count; i++)
                {
                    sql.AppendFormat("{0},", activeJobIDs[i]);
                }
                sql.Remove(sql.Length - 1, 1);
                sql.Append(")");
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql.ToString());
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        long id = reader.GetInt64(0);
                        res.Add(id);
                    }
                    reader.Close();
                }

            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public List<SearchJob> LoadSeardhJobs(string where, string orderby, SelectParameter[] parameters)
        {
            List<SearchJob> res = new List<SearchJob>();
            string selectSQL = String.Format(@"SELECT SearchJob.SearchJobID, SearchJob.Name, 
                                                      SearchJob.AllCriteria, SearchJob.Type, 
                                                      SearchJob.StartTime, SearchJob.EndTime, 
                                                      SearchJob.TotalFiles, 
                                                      SearchJob.TOTALLENGTH
                                               FROM SearchJob {0} {1}", where, orderby);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(selectSQL);

                foreach (SelectParameter parameter in parameters)
                {
                    db.AddInParameter(cmd, parameter.ParameterName, parameter.DbType, parameter.Value);
                }
                
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        SearchJob sj = CreateSearchJob(reader);
                        res.Add(sj);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res; 
        }

        public SearchJob CreateSearchJob(IDataReader reader)
        {
            SearchJob res = new SearchJob();
            res.SearchJobID = reader.GetInt64(0);
            res.Name = reader.GetString(1);
            res.AllCriteria = reader.GetString(2);
            res.Type = reader.GetByte(3);
            res.StartTime = reader.GetDateTime(4);
            res.EndTime = reader.GetDateTime(5);
            res.TotalFiles = reader.GetInt64(6);
            res.TotalLength = reader.GetInt64(7);
            return res;
        }

        #endregion

        #region insert

        private DbCommand insertCommand = null;

        private DbCommand GetInsertCommand(SqlDatabase db)
        {
            if (insertCommand == null)
            {
                string insertSQL = "INSERT INTO SearchJob (NAME, ALLCRITERIA, TYPE, STARTTIME, ENDTIME, TOTALFILES, TOTALLENGTH) VALUES (@NAME, @ALLCRITERIA, @TYPE, @STARTTIME, @ENDTIME, @TOTALFILES, @TOTALLENGTH) SET @SEARCHJOBID = SCOPE_IDENTITY()";
                insertCommand = db.GetSqlStringCommand(insertSQL);
                db.AddOutParameter(insertCommand, "@SEARCHJOBID", DbType.Int64, 8);
                db.AddInParameter(insertCommand, "@NAME", DbType.String);
                db.AddInParameter(insertCommand, "@ALLCRITERIA", DbType.String);
                db.AddInParameter(insertCommand, "@TYPE", DbType.Byte);
                db.AddInParameter(insertCommand, "@STARTTIME", DbType.DateTime);
                db.AddInParameter(insertCommand, "@ENDTIME", DbType.DateTime);
                db.AddInParameter(insertCommand, "@TOTALFILES", DbType.Int64);
                db.AddInParameter(insertCommand, "@TOTALLENGTH", DbType.Int64);
            }
            return insertCommand;
        }

        private static void SetInsertValues(DbCommand cmd, SearchJob entity)
        {
            cmd.Parameters["@NAME"].Value = entity.Name;
            cmd.Parameters["@ALLCRITERIA"].Value = entity.AllCriteria == null ? DBNull.Value : (object)entity.AllCriteria;
            cmd.Parameters["@TYPE"].Value = entity.Type == null ? DBNull.Value : (object)entity.Type;
            cmd.Parameters["@STARTTIME"].Value = entity.StartTime == null ? DBNull.Value : (object)entity.StartTime;
            cmd.Parameters["@ENDTIME"].Value = entity.EndTime == null ? DBNull.Value : (object)entity.EndTime;
            cmd.Parameters["@TOTALFILES"].Value = entity.TotalFiles;
            cmd.Parameters["@TOTALLENGTH"].Value = entity.TotalLength;
        }

        public bool InsertSearchJob(SearchJob entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.SearchJobID = (long)db.GetParameterValue(cmd, "@SEARCHJOBID");
            }
            return res;
        }

        #endregion

        #region update
        public bool UpdateSearchJob(long jobId, DateTime startdate, DateTime enddate)
        {
            bool res = false;
            string updateSql = @"update searchJob set 
	searchjob.StartTime = @STARTTIME,
	searchjob.EndTime = @ENDTIME,
	searchjob.TotalFiles = results.numFiles,
	searchjob.TOTALLENGTH = results.totLength
    from searchJob inner join 
		(select searchJobid, count(searchFileid) as numFiles, sum(length) as totLength 
		from searchfile group by searchJobid) 
		as results on searchJob.searchJobid = results.searchJobid
where searchJob.searchjobid = @SEARCHJOBID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@SEARCHJOBID", DbType.Int64, jobId);
            db.AddInParameter(cmd, "@STARTTIME", DbType.DateTime, startdate);
            db.AddInParameter(cmd, "@ENDTIME", DbType.DateTime, enddate);
            res = db.ExecuteNonQuery(cmd) == 1;
            return res;
        }
        #endregion

        #region delete
        public void ClearJobData(long jobID)
        {
            string deleteCmd = "dbo.ClearJobData";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetStoredProcCommand(deleteCmd);
            db.AddInParameter(cmd, "@SEARCHJOBID", DbType.Int64, jobID);
            db.ExecuteNonQuery(cmd);
        }

        public bool DeleteSearchJob(long jobID)
        {
            string deleteSql = "DELETE FROM searchJOb WHERE SearchJobID = @SEARCHJOBID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(deleteSql);
            db.AddInParameter(cmd, "@SEARCHJOBID", DbType.Int64, jobID);
            return db.ExecuteNonQuery(cmd) == 1;
        }

        #endregion

    }
}
